package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.entities.Statistics;
import com.netcracker.training.automonic.dao.entities.StatsType;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see StatsType
 */

public class OracleStatsType implements StatsType // Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static StatsType createStatsType(String code, String name)
	{
		StatsType statsType = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT seq_StatsTypes.NEXTVAL FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				int id = rs.getInt(1);
				statsType = new OracleStatsType(id, code, name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(statsType);
		}
		
		return statsType;
	}
	
	public static StatsType selectStatsTypeById(int id)
	{
		StatsType statsType = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_STATISTICS_TYPE_BY_ID, id);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				statsType = new OracleStatsType(id,
				                                rs.getString("code"),
				                                rs.getString("name"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(statsType);
		}
		
		return statsType;
	}

	public static StatsType selectStatsTypeByCode(String code)
	{
		StatsType statsType = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_STATISTICS_TYPE_BY_CODE, code);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				statsType = new OracleStatsType(rs.getInt("statstype_id"),
				                                code,
				                                rs.getString("name"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(statsType);
		}
		
		return statsType;
	}

	public static List<StatsType> selectStatsTypesByName(String name)
	{
		List<StatsType> statsTypes = new LinkedList<StatsType>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_STATISTICS_TYPES_BY_NAME, name);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				StatsType statsType = new OracleStatsType(
						rs.getInt("statstype_id"),
						rs.getString("code"),
						name);
				statsTypes.add(statsType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (StatsType statsType : statsTypes)
					LOGGER.trace(statsType);
		}
		
		return statsTypes;
	}
	
	public static List<StatsType> selectAllStatsTypes()
	{
		List<StatsType> statsTypes = new LinkedList<StatsType>();
		
		try (Statement stmt = con.createStatement())
		{
			ResultSet rs = stmt.executeQuery(SELECT_ALL_STATISTICS_TYPES);
			
			while (rs.next())
			{
				StatsType statsType = 
						new OracleStatsType(rs.getInt("statstype_id"),
						                    rs.getString("code"),
						                    rs.getString("name"));
				statsTypes.add(statsType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (StatsType statsType : statsTypes)
					LOGGER.trace(statsType);
		}
		
		return statsTypes;
	}
	
	private final int id;
	private String code = "",
	               name = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleStatsType(int id, String code, String name)
	{
		this.id = id;
		setCode(code);
		setName(name);
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public int getId()
	{
		return id;
	}

	@Override
	public String getCode()
	{
		return code;
	}

	@Override
	public void setCode(String code)
	{
		if (this.code.hashCode() != code.hashCode())
			modified = true;
		code = code.replaceAll("'{1,}", "''");
		this.code = code;
	}

	@Override
	public String getName()
	{
		return name;
	}

	@Override
	public void setName(String name)
	{
		if (this.name.hashCode() != name.hashCode())
			modified = true;
		name = name.replaceAll("'{1,}", "''");
		this.name = name;
	}

	/**
	 * Not implemented.
	 * 
	 * @return null
	 */
	@Deprecated
	@Override
	public List<Object> getObjects()
	{
		return null;
	}

	@Override
	public List<Statistics> getStatistics()
	{
		return OracleStatistics.selectStatisticsByType(this);
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		return String.format("%d [%s] %s", id, code, name);
	}
	
	@Override
	public void save()
	{
		boolean statsTypeExists = (selectStatsTypeById(id) != null);
		
		if (statsTypeExists)
		{
			if (modified)
			{
				String sql = String.format(
						"UPDATE StatsTypes "
					  + "SET code = '%s', "
					  + "    name = '%s' "
					  + "WHERE statstype_id = %d",
						code, name, id);
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			String sql = String.format(
					"INSERT INTO StatsTypes "
				  + "	VALUES (%d, '%s', '%s')",
					id, code, name);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved stats type: " + this);
	}

}
